package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.entity.Attachs;
import cn.gson.crm.model.entity.Student;
import cn.gson.crm.model.po.JobFollowPo;

public class JobFollowDao {
	
	/**
	 * 关联附件
	 * @param recordId
	 * @param atts
	 * @throws SQLException
	 */
	public void connectAttach(int jobrecordId, String atts[] ) throws SQLException{
		String sql = "update attach set record = ? where id = ?";
		for (String aid : atts) {
			Jdbc.getInstace().executeUpdate(sql, jobrecordId, aid);
		}
	}
	
	/**
	 * 显示一次所有上传附件
	 * @param record
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public List<Attachs> showAllAttach(int record, String model) throws SQLException{
		String sql = "select * from attach where record = ? and model = ? ";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, record, model);
		List<Attachs> attach = null;
		
		if(data != null){
			attach = new ArrayList<>();	
			for (Map<String, Object> map : data) {
				Attachs att = new Attachs();
				if(map.get("id") != null){
					att.setId(Integer.parseInt(map.get("id").toString()));	
				}
				if(map.get("file_name") != null){
					att.setFileName((String)map.get("file_name"));
				}
				if(map.get("file_path") != null){
					att.setFilePath((String)map.get("file_path"));
				}
				if(map.get("model") != null){
					att.setModel((String)map.get("model"));
				}
				att.setRecord(Long.parseLong(map.get("record").toString()));
				att.setUploadTime((Date)map.get("upload_time"));
				att.setFileSize(Long.parseLong(map.get("file_size").toString()));
				attach.add(att);
			}
		}
		return attach;
	}
	
	public List<Attachs> showAllmianshiAttach(String model) throws SQLException{
		String sql = "select * from attach where model = ?";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql,model);
		List<Attachs> attach = null;
		if(data != null){
			attach = new ArrayList<>();	
			for (Map<String, Object> map : data) {
				Attachs att = new Attachs();
				if(map.get("id") != null){
					att.setId(Integer.parseInt(map.get("id").toString()));	
				}
				if(map.get("file_name") != null){
					att.setFileName((String)map.get("file_name"));
				}
				if(map.get("file_path") != null){
					att.setFilePath((String)map.get("file_path"));
				}
				if(map.get("model") != null){
					att.setModel((String)map.get("model"));
				}
				if(map.get("record") != null){
					att.setRecord(Long.parseLong(map.get("record").toString()));	
				}
				
				att.setUploadTime((Date)map.get("upload_time"));
				if(map.get("file_size") != null){
					att.setFileSize(Long.parseLong(map.get("file_size").toString()));
				}
				
				attach.add(att);
			}
		}
			return attach;
	}
	
	// 一页显示记录条数
	private int pageSize = 3;

	/**
	 * 创建一个学生的面试跟进记录
	 * 
	 * @param followPo
	 * @return
	 * @throws SQLException
	 */
	public Integer insertInterviewRecord(JobFollowPo followPo) throws SQLException {
		String insertSql = "INSERT into job_follow (stu_id,emp_id,interview_time,company,company_type,com_address,res_wage,salary,res_duration,professional) VALUES(?,?,?,?,?,?,?,?,?,?)";
		if(Jdbc.getInstace().executeUpdate(insertSql, followPo.getId(), followPo.getEmpId(), followPo.getInterDate(),
				followPo.getCompany(),followPo.getCompanyType(), followPo.getComAddress(), followPo.getResSalary(), followPo.getSalary(),
				followPo.getResDuration(), followPo.getProfessional())){
			String sql = "select jobrecord_id from job_follow order by jobrecord_id desc limit 1";
			Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
			if (map != null) {
				return Integer.parseInt(map.get("jobrecord_id").toString());
			}
		}
		return null;
	}

	/**
	 * 获取等级为gadeId状态为status的学生就业跟进记录
	 * 
	 * @param gadeId
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public List<JobFollowPo> getJobFollowList(Integer page, String gadeId, String status, String name, String phone)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "SELECT * FROM `student`join job_follow on  student.id = job_follow.stu_id where student.grade_id=?";
		List<Object> params = new ArrayList<>();
		params.add(gadeId);
		sql = buildParamsAndSql(params, sql, name, phone);
		sql += "  ORDER by job_follow.create_time desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<JobFollowPo> poList = null;
		if (rows != null) {
			poList = new ArrayList<>();
			for (Map<String, Object> row : rows) {
				JobFollowPo po = null;
				poList.add(buildJobFollowPo(row,po));
			}
		}
		return poList;

	}
	
	/**
	 * 显示最新面试的三位学生
	 * @param status
	 * @param isAllocation
	 * @return
	 * @throws SQLException
	 */
	public List<JobFollowPo> displayNewMSStu(String status,String isAllocation,int gradeId) throws SQLException{
		String sql = "SELECT * FROM  student s join job_follow j on  s.id = j.stu_id where s.stu_status=? and s.is_allocation = ? and s.grade_id=? order by j.create_time desc limit 3";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, status,isAllocation,gradeId);
		List<JobFollowPo> poList = null;
		if (rows != null) {
			poList = new ArrayList<>();
			for (Map<String, Object> row : rows) {
				JobFollowPo po = null;
				poList.add(buildJobFollowPo(row,po));
			}
		}
		return poList;
	}

	/**
	 * 获取等级为gadeId状态为status的学生就业跟进记录总页数
	 * @param gadeId
	 * @param status
	 * @param name
	 * @param phone
	 * @return
	 * @throws SQLException 
	 */
	public Integer getJobFollowListTotalPage(String gadeId, String status, String name, String phone) throws SQLException {
		String sql = "SELECT count(*)total FROM `student`join job_follow on student.id = job_follow.stu_id where student.grade_id=?";
		List<Object> params = new ArrayList<>();
		params.add(gadeId);
		sql = buildParamsAndSql(params, sql, name, phone);
		Map<String,Object> row = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if(row!=null){
			count=Integer.parseInt(row.get("total").toString());
		}
		return (int) Math.ceil(count.doubleValue()/pageSize);
	}

	/**
	 * 通过学生id查找就业跟进记录
	 * @param stuId
	 * @return
	 * @throws SQLException 
	 */
	public JobFollowPo getJobFollowByStuId(String stuId) throws SQLException{
		String sql="select * FROM job_follow WHERE stu_id=?";
		Map<String,Object> row = Jdbc.getInstace().executeQuery(sql, stuId);
		JobFollowPo po = null;
		if(row!=null){
			po = buildJobFollowPo(row,po);
		}
		return po;
	}
	
	/**
	 * 通过学生id查找就业跟进记录
	 * @param stuId
	 * @return
	 * @throws SQLException 
	 */
	public JobFollowPo getJobFollowByStuId1(String stuId) throws SQLException{
		String sql="select m.*,(select count(1) from attach a where a.record = m.jobrecord_id and a.model='job_mianshi') attach from job_follow m "
				+ "WHERE stu_id=?";
		Map<String,Object> row = Jdbc.getInstace().executeQuery(sql, stuId);
		JobFollowPo po = null;
		if(row!=null){
			po = buildJobFollowPo(row,po);
		}
		return po;
	}
	
	/**
	 * 修改就业跟进记录
	 * @param recordId
	 * @return
	 * @throws SQLException 
	 */
	public Boolean changeJobFollowRecord(JobFollowPo followPo) throws SQLException{
		String sql = "update job_follow set emp_id = ?,interview_time=?,company=?,company_type=?,com_address=?,res_wage=?,"
				+ "salary=?,res_duration=?,professional=? where jobrecord_id=?";
		return Jdbc.getInstace().executeUpdate(sql, followPo.getEmpId(),followPo.getInterDate(),followPo.getCompany().trim(),followPo.getCompanyType().trim(),
				followPo.getComAddress().trim(),followPo.getResSalary().trim(),followPo.getSalary().trim(),followPo.getResDuration().trim(),followPo.getProfessional().trim(),followPo.getRecordId());
	}
	/**
	 * 构建sql和参数
	 * 
	 * @param params
	 * @param sql
	 * @param name
	 * @param phone
	 * @return
	 * @throws SQLException
	 */
	private String buildParamsAndSql(List<Object> params, String sql, String name, String phone) throws SQLException {
		if (!StringUtils.isNullOrEmpty(name)) {
			sql += " and student.stu_name like ?";
			params.add("%" + name.trim() + "%");
		} 
			if (!StringUtils.isNullOrEmpty(phone)) {
				sql += " and student.stu_phone like ? ";
				params.add("%" + phone.trim() + "%");
			
		}
		return sql;

	}

	/**
	 * 构建JobFollow对象
	 * 
	 * @param row
	 * @return
	 * @throws NumberFormatException
	 * @throws SQLException
	 */
	private JobFollowPo buildJobFollowPo(Map<String, Object> row,JobFollowPo po) throws NumberFormatException, SQLException {
		 po = new JobFollowPo();
		if (row.get("jobrecord_id") != null) {
			po.setRecordId(Integer.parseInt(row.get("jobrecord_id").toString()));
		}
		if (row.get("stu_id") != null) {
			po.setId(Integer.parseInt(row.get("stu_id").toString()));
		}
		po.setPhone((String) row.get("stu_phone"));
		po.setName((String) row.get("stu_name"));
		po.setCreateTime((Date) row.get("create_time"));
		po.setComAddress((String) row.get("com_address"));
		if(row.get("company") != null){
			po.setCompany((String)row.get("company"));
		}
		
		if(row.get("company_type") != null){
			po.setCompanyType((String)row.get("company_type"));	
		}
		
		po.setProfessional((String)row.get("professional"));
		if (row.get("emp_id") != null) {
			po.setEmpId(Integer.parseInt(row.get("emp_id").toString()));
			// 获取创建员工的姓名
			po.setCreator(
					new EmploDao().emploGetById("正常", Integer.parseInt(row.get("emp_id").toString())).getUserName());
		}
		po.setResSalary((String) row.get("res_wage"));
		if(row.get("salary") != null){
			po.setSalary((String) row.get("salary"));
		}
		po.setInterDate((Date) row.get("interview_time"));
		po.setResDuration((String) row.get("res_duration"));
		if(row.get("attach") !=null){
			po.setAttach(Integer.parseInt(row.get("attach").toString()));
		}
		return po;
	}

	/**
	 * 取到集合之后再进行分页
	 * 
	 * @param request
	 * @param list
	 * @param pageSize
	 * @param page
	 * @return
	 */
	public List<JobFollowPo> getListThenDividPage(HttpServletRequest request, List<JobFollowPo> list, int page) {
		List<JobFollowPo> subList = null;
		Integer totalCount = list.size();
		/* 计算出总共能分成多少页 */
		int pageCount = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
		if (totalCount > 0) {
			if (page <= pageCount) {
				// 当前页数为第一页
				if (page == 1) {
					// 数据总数小于每页显示的数据条数
					if (totalCount <= pageSize) {
						// 截止到总的数据条数(当前数据不足一页，按一页显示)，这样才不会出现数组越界异常
						subList = list.subList(0, totalCount);
					} else {
						subList = list.subList(0, pageSize);
					}
				} else {
					// 截取起始下标
					int fromIndex = (page - 1) * pageSize;
					// 截取截止下标
					int toIndex = page * pageSize;
					/* 计算截取截止下标 */
					if ((totalCount - toIndex) % pageSize >= 0) {
						toIndex = page * pageSize;
					} else {
						toIndex = (page - 1) * pageSize + (totalCount % pageSize);
					}
					if (totalCount >= toIndex) {
						subList = list.subList(fromIndex, toIndex);
					}
				}
			} else {
				subList = null;
			}
		}
		request.setAttribute("totalPage", pageCount);
		return subList;
	}

}
